Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Comments

You can associate comments with SQL statements and schema objects.

## Comments Within SQL Statements

Comments can make your application easier for you to read and maintain. For example, you can include a comment in a statement that describes the purpose of the statement within your application. With the exception of hints, comments within SQL statements do not affect the statement execution. Please refer to "Using Hints" on using this particular form of comment.

A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:

- Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.

- Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

Some of the tools used to enter SQL have additional restrictions. For example, if you are using SQL*Plus, by default you cannot have a blank line inside a multiline comment. For more information, please refer to the documentation for the tool you use as an interface to the database.

A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.

Example These statements contain many comments:

    SELECT last_name, salary + NVL(commission_pct, 0),
       job_id, e.department_id
    /* Select all employees whose compensation is
    greater than that of Pataballa.*/
      FROM employees e, departments d
           /*The DEPARTMENTS table is used to get the department name.*/
      WHERE e.department_id = d.department_id
        AND salary + NVL(commission_pct,0) >   /* Subquery:       */
       (SELECT salary + NVL(commission_pct,0)
                     /* total compensation is salar + commission_pct */
          FROM employees
          WHERE last_name = 'Pataballa');

    SELECT last_name,                    -- select the name
        salary + NVL(commission_pct, 0),-- total compensation
        job_id,                         -- job
        e.department_id                 -- and department
      FROM employees e,                 -- of all employees
           departments d
      WHERE e.department_id = d.department_id
        AND salary + NVL(commission_pct, 0) >  -- whose compensation
                                               -- is greater than
          (SELECT salary + NVL(commission_pct,0)  -- the compensation
        FROM employees
        WHERE last_name = 'Pataballa')        -- of Pataballa.
    ;

## Comments on Schema Objects

You can associate a comment with a table, view, materialized view, or column using the `COMMENT` command. Comments associated with schema objects are stored in the data dictionary. Please refer to COMMENT for a description of comments.

## Using Hints

You can use comments in a SQL statement to pass instructions, or hints, to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.

Note: Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the `EXPLAIN PLAN` statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.

A statement block can have only one comment containing hints, and that comment must follow the `SELECT`, `UPDATE`, `INSERT`, `MERGE`, or `DELETE` keyword. Only two hints are used with `INSERT` statements: The `APPEND` hint always follows the `INSERT` keyword, and the `PARALLEL` hint can follow the `INSERT` keyword.

The following syntax diagram shows hints contained in both styles of comments that Oracle supports within a statement block. The hint syntax must follow immediately after an `INSERT`, `UPDATE`, `DELETE`, `SELECT`, or `MERGE` keyword that begins the statement block.

hint::=

Description of the illustration hint.gif

where:

- The plus sign (+) causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter. No space is permitted.

- `hint` is one of the hints discussed in this section. The space between the plus sign and the hint is optional. If the comment contains multiple hints, then separate the hints by at least one space.

- `string` is other commenting text that can be interspersed with the hints.

The `--+` syntax requires that the entire comment be on a single line.

Oracle Database ignores hints and does not return an error under the following circumstances:

- The hint contains misspellings or syntax errors. However, the database does consider other correctly specified hints in the same comment.

- The comment containing the hint does not follow a `DELETE`, `INSERT`, `MERGE`, `SELECT`, or `UPDATE` keyword.

- A combination of hints conflict with each other. However, the database does consider other hints in the same comment.

- The database environment uses PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5.

Many hints can apply both to specific tables or indexes and more globally to tables within a view or to columns that are part of indexes. The syntactic elements `tablespec` and `indexspec` define these global hints.

tablespec::=

Description of the illustration tablespec.gif

You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. However, do not include the schema name with the table name within the hint, even if the schema name appears in the statement.

See Also: Oracle Database Performance Tuning Guide for information on the following topics: When to use global hints and how Oracle interprets them Using `EXPLAIN PLAN` to learn how the optimizer is executing a query References in hints to tables within views

indexspec::=

Description of the illustration indexspec.gif

When `tablespec` is followed by `indexspec` in the specification of a hint, a comma separating the table name and index name is permitted but not required. Commas are also permitted, but not required, to separate multiple occurrences of `indexspec`.

Specifying a Query Block in a Hint

You can specify an optional query block name in many hints to specify the query block to which the hint applies. This syntax lets you specify in the outer query a hint that applies to an inline view. When you specify a hint in the query block itself to which the hint applies, you omit the `@queryblock` syntax.

The syntax of the query block argument is of the form `@``queryblock`, where `queryblock` is an identifier that specifies a query block in the query. The `queryblock` identifier can either be system-generated or user-specified.

- The system-generated identifier can be obtained by using `EXPLAIN` `PLAN` for the query. Pretransformation query block names can be determined by running `EXPLAIN` `PLAN` for the query using the `NO_QUERY_TRANSFORMATION` hint. See "NO_QUERY_TRANSFORMATION Hint".

- The user-specified name can be set with the `QB_NAME` hint. See "QB_NAME Hint".

Table 2-21 lists the hints by functional category and contains cross-references to the syntax and semantics for each hint. An alphabetical listing of the hints follows the table.

See Also: Oracle Database Performance Tuning Guide for information on: using hints to optimize SQL statements and on detailed information about using the `tablespec` and `indexspec` syntax specifying a query block in a hint descriptions of hint categories and when to use them

Table 2-21 Hints by Functional Category

Hint | Link to Syntax and Semantics
--- | ---
Optimization Goals and Approaches | ALL_ROWS Hint FIRST_ROWS Hint
-- | RULE Hint
Access Path Hints | CLUSTER Hint
-- | FULL Hint
-- | HASH Hint
-- | INDEX Hint NO_INDEX Hint
-- | INDEX_ASC Hint INDEX_DESC Hint
-- | INDEX_COMBINE Hint
-- | INDEX_JOIN Hint
-- | INDEX_FFS Hint
-- | INDEX_SS Hint
-- | INDEX_SS_ASC Hint
-- | INDEX_SS_DESC Hint
-- | NO_INDEX_FFS Hint
-- | NO_INDEX_SS Hint
Join Order Hints | ORDERED Hint
-- | LEADING Hint
Join Operation Hints | USE_HASH Hint NO_USE_HASH Hint
-- | USE_MERGE Hint NO_USE_MERGE Hint
-- | USE_NL Hint USE_NL_WITH_INDEX Hint NO_USE_NL Hint
Parallel Execution Hints | PARALLEL Hint NO_PARALLEL Hint
-- | PARALLEL_INDEX Hint NO_PARALLEL_INDEX Hint
-- | PQ_DISTRIBUTE Hint
Query Transformation Hints | FACT Hint NO_FACT Hint
-- | MERGE Hint NO_MERGE Hint
-- | NO_EXPAND Hint USE_CONCAT Hint
-- | REWRITE Hint NO_REWRITE Hint
-- | UNNEST Hint NO_UNNEST Hint
-- | STAR_TRANSFORMATION Hint NO_STAR_TRANSFORMATION Hint
-- | NO_QUERY_TRANSFORMATION Hint
Other Hints | APPEND Hint NOAPPEND Hint
-- | CACHE Hint NOCACHE Hint
-- | CURSOR_SHARING_EXACT Hint
-- | DRIVING_SITE Hint
-- | DYNAMIC_SAMPLING Hint
-- | PUSH_PRED Hint NO_PUSH_PRED Hint
-- | PUSH_SUBQ Hint NO_PUSH_SUBQ Hint
-- | PX_JOIN_FILTER Hint NO_PX_JOIN_FILTER Hint
-- | NO_XML_QUERY_REWRITE Hint
-- | QB_NAME Hint
-- | MODEL_MIN_ANALYSIS Hint

## Alphabetical Listing of Hints

This section provides syntax and semantics for all hints in alphabetical order.

### ALL_ROWS Hint

Description of the illustration all_rows_hint.gif

The `ALL_ROWS` hint instructs the optimizer to optimize a statement block with a goal of best throughput—that is, minimum total resource consumption. For example, the optimizer uses the query optimization approach to optimize this statement for best throughput:

    SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
      FROM employees
      WHERE employee_id = 7566;

If you specify either the `ALL_ROWS` or the `FIRST_ROWS` hint in a SQL statement, and if the data dictionary does not have statistics about tables accessed by the statement, then the optimizer uses default statistical values, such as allocated storage for such tables, to estimate the missing statistics and to subsequently choose an execution plan. These estimates might not be as accurate as those gathered by the `DBMS_STATS` package, so you should use the `DBMS_STATS` package to gather statistics.

If you specify hints for access paths or join operations along with either the `ALL_ROWS` or `FIRST_ROWS` hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.

### APPEND Hint

Description of the illustration append_hint.gif

The `APPEND` hint instructs the optimizer to use direct-path `INSERT` if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional `INSERT` is the default in serial mode, and direct-path `INSERT` is the default in parallel mode.

In direct-path `INSERT`, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path `INSERT` can be considerably faster than conventional `INSERT`.

See Also: Oracle Database Administrator's Guide for information on direct-path inserts

### CACHE Hint

Description of the illustration cache_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `CACHE` hint instructs the optimizer to place the blocks retrieved for the table at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This hint is useful for small lookup tables.

In the following example, the `CACHE` hint overrides the default caching specification of the table:

    SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
      FROM employees hr_emp;

The `CACHE` and `NOCACHE` hints affect system statistics `table scans (long tables)` and `table scans (short tables)`, as shown in the `V$SYSSTAT` data dictionary view.

### CLUSTER Hint

Description of the illustration cluster_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `CLUSTER` hint instructs the optimizer to use a cluster scan to access the specified table. This hint applies only to clustered tables.

### CURSOR_SHARING_EXACT Hint

Description of the illustration cursor_sharing_exact_hint.gif

Oracle can replace literals in SQL statements with bind variables, when it is safe to do so. This replacement is controlled with the `CURSOR_SHARING` initialization parameter. The `CURSOR_SHARING_EXACT` hint instructs the optimizer to switch this behavior off. In other words, Oracle executes the SQL statement without any attempt to replace literals with bind variables.

### DRIVING_SITE Hint ``

Description of the illustration driving_site_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `DRIVING_SITE` hint instructs the optimizer to execute the query at a different site than that selected by the database. This hint is useful if you are using distributed query optimization.

For example:

    SELECT /*+ DRIVING_SITE(departments) */ *
      FROM employees, departments@rsite
      WHERE employees.department_id = departments.department_id;

If this query is executed without the hint, then rows from `departments` are sent to the local site, and the join is executed there. With the hint, the rows from `employees` are sent to the remote site, and the query is executed there and the result set is returned to the local site.

### DYNAMIC_SAMPLING Hint

Description of the illustration dynamic_sampling_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `DYNAMIC_SAMPLING` hint instructs the optimizer how to control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes.

You can set the value of `DYNAMIC_SAMPLING` to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify `tablespec`.

The `integer` value is `0` to `10,` indicating the degree of sampling.

If a cardinality statistic already exists for the table, then the optimizer uses it. Otherwise, the optimizer enables dynamic sampling to estimate the cardinality statistic.

If you specify `tablespec` and the cardinality statistic already exists, then:

- If there is no single-table predicate (a `WHERE` clause that evaluates only one table), then the optimizer trusts the existing statistics and ignores this hint. For example, the following query will not result in any dynamic sampling if `employees` is analyzed:

      SELECT /*+ dynamic_sampling(e 1) */ count(*)
        FROM employees e;

- If there is a single-table predicate, then the optimizer uses the existing cardinality statistic and estimates the selectivity of the predicate using the existing statistics.

To apply dynamic sampling to a specific table, use the following form of the hint:

    SELECT /*+ dynamic_sampling(employees 1) */ *
      FROM employees
      WHERE ..,

See Also: Oracle Database Performance Tuning Guide for information about dynamic sampling and the sampling levels that you can set

### FACT Hint

Description of the illustration fact_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `FACT` hint is used in the context of the star transformation. It instructs the optimizer that the table specified in `tablespec` should be considered as a fact table.

### FIRST_ROWS Hint

Description of the illustration first_rows_hint.gif

The `FIRST_ROWS` hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first `n` rows most efficiently. For `integer`, specify the number of rows to return.

Note: The `FIRST_ROWS` hint specified without an argument, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability only.

For example, the optimizer uses the query optimization approach to optimize the following statement for best response time:

    SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
      FROM employees
      WHERE department_id = 20;

In this example each department contains many employees. The user wants the first 10 employees of department 20 to be displayed as quickly as possible.

The optimizer ignores this hint in `DELETE` and `UPDATE` statement blocks and in `SELECT` statement blocks that include any blocking operations, such as sorts or groupings. Such statements cannot be optimized for best response time, because Oracle Database must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any such statement, then the database optimizes for best throughput.

See Also: "ALL_ROWS Hint" for additional information on the `FIRST_ROWS` hint and statistics

### FULL Hint

Description of the illustration full_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `FULL` hint instructs the optimizer to perform a full table scan for the specified table. For example:

    SELECT /*+ FULL(e) */ employee_id, last_name
      FROM hr.employees e
      WHERE last_name LIKE :b1;

Oracle Database performs a full table scan on the `employees` table to execute this statement, even if there is an index on the `last_name` column that is made available by the condition in the `WHERE` clause.

The `employees` table has alias `e` in the `FROM` clause, so the hint must refer to the table by its alias rather than by its name. Do not specify schema names in the hint even if they are specified in the `FROM` clause.

### HASH Hint

Description of the illustration hash_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `HASH` hint instructs the optimizer to use a hash scan to access the specified table. This hint applies only to tables stored in a table cluster.

### INDEX Hint

Description of the illustration index_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `INDEX` hint instructs the optimizer to use an index scan for the specified table. You can use the `INDEX` hint for function-based, domain, B-tree, bitmap, and bitmap join indexes.

The behavior of the hint depends on the `indexspec` specification:

- If the `INDEX` hint specifies a single available index, then the database performs a scan on this index. The optimizer does not consider a full table scan or a scan of another index on the table.

- For a hint on a combination of multiple indexes, Oracle recommends using `INDEX_COMBINE` rather than `INDEX`, because it is a more versatile hint. If the `INDEX` hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost. The database does not consider a full table scan or a scan on an index not listed in the hint.

- If the `INDEX` hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.

For example:

    SELECT /*+ INDEX (employees emp_department_ix)*/
           employee_id, department_id
      FROM employees
      WHERE department_id > 50;

### INDEX_ASC Hint

Description of the illustration index_asc_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `INDEX_ASC` hint instructs the optimizer to use an index scan for the specified table. If the statement uses an index range scan, then Oracle Database scans the index entries in ascending order of their indexed values. Each parameter serves the same purpose as in "INDEX Hint".

The default behavior for a range scan is to scan index entries in ascending order of their indexed values, or in descending order for a descending index. This hint does not change the default order of the index, and therefore does not specify anything more than the `INDEX` hint. However, you can use the `INDEX_ASC` hint to specify ascending range scans explicitly should the default behavior change.

### INDEX_COMBINE Hint ``

Description of the illustration index_combine_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `INDEX_COMBINE` hint instructs the optimizer to use a bitmap access path for the table. If `indexspec` is omitted from the `INDEX_COMBINE` hint, then the optimizer uses whatever Boolean combination of indexes has the best cost estimate for the table. If you specify `indexspec`, then the optimizer tries to use some Boolean combination of the specified indexes. Each parameter serves the same purpose as in "INDEX Hint". For example:

    SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
      FROM employees e
      WHERE manager_id = 108
         OR department_id = 110;

### INDEX_DESC Hint

Description of the illustration index_desc_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `INDEX_DESC` hint instructs the optimizer to use a descending index scan for the specified table. If the statement uses an index range scan and the index is ascending, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition. For a descending index, this hint effectively cancels out the descending order, resulting in a scan of the index entries in ascending order. Each parameter serves the same purpose as in "INDEX Hint". For example:

    SELECT /*+ INDEX_DESC(e emp_name_ix) */ *
      FROM employees e;

See Also: Oracle Database Performance Tuning Guide for information on full scans

### INDEX_FFS Hint

Description of the illustration index_ffs_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `INDEX_FFS` hint instructs the optimizer to perform a fast full index scan rather than a full table scan.

Each parameter serves the same purpose as in "INDEX Hint". For example:

    SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name
      FROM employees e;

### INDEX_JOIN Hint

Description of the illustration index_join_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `INDEX_JOIN` hint instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.

Each parameter serves the same purpose as in "INDEX Hint". For example, the following query uses an index join to access the `manager_id` and `department_id` columns, both of which are indexed in the `employees` table.

    SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id
      FROM employees e
      WHERE manager_id < 110
        AND department_id < 50;

### INDEX_SS Hint

Description of the illustration index_ss_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `INDEX_SS` hint instructs the optimizer to perform an index skip scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.

Each parameter serves the same purpose as in "INDEX Hint". For example:

    SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name
      FROM employees e
      WHERE first_name = 'Steven';

See Also: Oracle Database Performance Tuning Guide for information on index skip scans

### INDEX_SS_ASC Hint

Description of the illustration index_ss_asc_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `INDEX_SS_ASC` hint instructs the optimizer to perform an index skip scan for the specified table. If the statement uses an index range scan, then Oracle Database scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition. Each parameter serves the same purpose as in "INDEX Hint".

The default behavior for a range scan is to scan index entries in ascending order of their indexed values, or in descending order for a descending index. This hint does not change the default order of the index, and therefore does not specify anything more than the `INDEX_SS` hint. However, you can use the `INDEX_SS_ASC` hint to specify ascending range scans explicitly should the default behavior change.

See Also: Oracle Database Performance Tuning Guide for information on index skip scans

### INDEX_SS_DESC Hint

Description of the illustration index_ss_desc_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `INDEX_SS_DESC` hint instructs the optimizer to perform an index skip scan for the specified table. If the statement uses an index range scan and the index is ascending, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition. For a descending index, this hint effectively cancels out the descending order, resulting in a scan of the index entries in ascending order.

Each parameter serves the same purpose as in the "INDEX Hint". For example:

    SELECT /*+ INDEX_SS_DESC(e emp_name_ix) */ last_name
      FROM employees e
      WHERE first_name = 'Steven';

See Also: Oracle Database Performance Tuning Guide for information on index skip scans

### LEADING Hint

Description of the illustration leading_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `LEADING` hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. This hint is more versatile than the `ORDERED` hint. For example:

    SELECT /*+ LEADING(e j) */ *
        FROM employees e, departments d, job_history j
        WHERE e.department_id = d.department_id
          AND e.hire_date = j.start_date;

The `LEADING` hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting `LEADING` hints, then all of them are ignored. If you specify the `ORDERED` hint, it overrides all `LEADING` hints.

### MERGE Hint

Description of the illustration merge_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `MERGE` hint lets you merge views in a query.

If a view's query block contains a `GROUP BY` clause or `DISTINCT` operator in the `SELECT` list, then the optimizer can merge the view into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an `IN` subquery into the accessing statement if the subquery is uncorrelated.

For example:

    SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
       FROM employees e1,
       (SELECT department_id, avg(salary) avg_salary
          FROM employees e2
          GROUP BY department_id) v
       WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;

When the `MERGE` hint is used without an argument, it should be placed in the view query block. When `MERGE` is used with the view name as an argument, it should be placed in the surrounding query.

### MODEL_MIN_ANALYSIS Hint

Description of the illustration model_min_analysis_hint.gif

The `MODEL_MIN_ANALYSIS` hint instructs the optimizer to omit some compile-time optimizations of spreadsheet rules—primarily detailed dependency graph analysis. Other spreadsheet optimizations, such as creating filters to selectively populate spreadsheet access structures and limited rule pruning, are still used by the optimizer.

This hint reduces compilation time because spreadsheet analysis can be lengthy if the number of spreadsheet rules is more than several hundreds.

### NOAPPEND Hint ``

Description of the illustration noappend_hint.gif

The `NOAPPEND` hint instructs the optimizer to use conventional `INSERT` by disabling parallel mode for the duration of the `INSERT` statement. Conventional `INSERT` is the default in serial mode, and direct-path `INSERT` is the default in parallel mode.

### NOCACHE Hint

Description of the illustration nocache_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `NOCACHE` hint instructs the optimizer to place the blocks retrieved for the table at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache. For example:

    SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name
      FROM employees hr_emp;

The `CACHE` and `NOCACHE` hints affect system statistics `table` `scans(long` `tables)` and `table` `scans(short` `tables)`, as shown in the `V$SYSSTAT` view.

See Also: Oracle Database Performance Tuning Guide for information on automatic caching of tables, depending on their size

### NO_EXPAND Hint

Description of the illustration no_expand_hint.gif

(See "Specifying a Query Block in a Hint")

The `NO_EXPAND` hint instructs the optimizer not to consider `OR`-expansion for queries having `OR` conditions or `IN`-lists in the `WHERE` clause. Usually, the optimizer considers using `OR` expansion and uses this method if it decides that the cost is lower than not using it. For example:

    SELECT /*+ NO_EXPAND */ *
      FROM employees e, departments d
      WHERE e.manager_id = 108
         OR d.department_id = 110;

See Also: Oracle Database Performance Tuning Guide for a discussion of OR-expansion the "USE_CONCAT Hint", which is the opposite of this hint

### NO_FACT Hint

Description of the illustration no_fact_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `NO_FACT` hint is used in the context of the star transformation. It instruct the optimizer that the queried table should not be considered as a fact table.

### NO_INDEX Hint

Description of the illustration no_index_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `NO_INDEX` hint instructs the optimizer not to use one or more indexes for the specified table. For example:

    SELECT /*+ NO_INDEX(employees emp_empid) */ employee_id
      FROM employees
      WHERE employee_id > 200;

Each parameter serves the same purpose as in "INDEX Hint" with the following modifications:

- If this hint specifies a single available index, then the optimizer does not consider a scan on this index. Other indexes not specified are still considered.

- If this hint specifies a list of available indexes, then the optimizer does not consider a scan on any of the specified indexes. Other indexes not specified in the list are still considered.

- If this hint specifies no indexes, then the optimizer does not consider a scan on any index on the table. This behavior is the same as a `NO_INDEX` hint that specifies a list of all available indexes for the table.

The `NO_INDEX` hint applies to function-based, B-tree, bitmap, cluster, or domain indexes. If a `NO_INDEX` hint and an index hint (`INDEX`, `INDEX_ASC`, `INDEX_DESC`, `INDEX_COMBINE`, or `INDEX_FFS`) both specify the same indexes, then the database ignores both the `NO_INDEX` hint and the index hint for the specified indexes and considers those indexes for use during execution of the statement.

### NO_INDEX_FFS Hint

Description of the illustration no_index_ffs_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `NO_INDEX_FFS` hint instructs the optimizer to exclude a fast full index scan of the specified indexes on the specified table. Each parameter serves the same purpose as in the "INDEX Hint". For example:

    SELECT /*+ NO_INDEX_FFS(items item_order_ix) */ order_id
      FROM order_items items;

### NO_INDEX_SS Hint

Description of the illustration no_index_ss_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `NO_INDEX_SS` hint instructs the optimizer to exclude a skip scan of the specified indexes on the specified table. Each parameter serves the same purpose as in the "INDEX Hint".

See Also: Oracle Database Performance Tuning Guide for information on index skip scans

### NO_MERGE Hint

Description of the illustration no_merge_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `NO_MERGE` hint instructs the optimizer not to combine the outer query and any inline view queries into a single query.

This hint lets you have more influence over the way in which the view is accessed. For example, the following statement causes view `seattle_dept` not to be merged.:

    SELECT /*+NO_MERGE(seattle_dept)*/ e1.last_name, seattle_dept.department_name
      FROM employees e1,
        (SELECT location_id, department_id, department_name
           FROM departments
           WHERE location_id = 1700) seattle_dept
      WHERE e1.department_id = seattle_dept.department_id;

When you use the `NO_MERGE` hint in the view query block, specify it without an argument. When you specify `NO_MERGE` in the surrounding query, specify it with the view name as an argument.

### NO_PARALLEL Hint

Description of the illustration no_parallel_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `NO_PARALLEL` hint overrides a `PARALLEL` parameter in the DDL that created or altered the table. For example:

    SELECT /*+ NO_PARALLEL(hr_emp) */ last_name
      FROM employees hr_emp;

### NOPARALLEL Hint

The `NOPARALLEL` hint has been deprecated. Use the `NO_PARALLEL` hint instead.

### NO_PARALLEL_INDEX Hint

Description of the illustration no_parallel_index_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `NO_PARALLEL_INDEX` hint overrides a `PARALLEL` parameter in the DDL that created or altered the index, thus avoiding a parallel index scan operation.

### NOPARALLEL_INDEX Hint

The `NOPARALLEL_INDEX` hint has been deprecated. Use the `NO_PARALLEL_INDEX` hint instead.

### NO_PUSH_PRED Hint

Description of the illustration no_push_pred_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `NO_PUSH_PRED` hint instructs the optimizer not to push a join predicate into the view. For example:

    SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *
        FROM employees e,
             (SELECT manager_id
                FROM employees
             ) v
      WHERE e.manager_id = v.manager_id(+)
        AND e.employee_id = 100;

### NO_PUSH_SUBQ Hint

Description of the illustration no_push_subq_hint.gif

(See "Specifying a Query Block in a Hint")

The `NO_PUSH_SUBQ` hint instructs the optimizer to evaluate nonmerged subqueries as the last step in the execution plan. Doing so can improve performance if the subquery is relatively expensive or does not reduce the number of rows significantly.

### NO_PX_JOIN_FILTER Hint

Description of the illustration no_px_join_filter_hint.gif

This hint prevents the optimizer from using parallel join bitmap filtering.

### NO_REWRITE Hint

Description of the illustration no_rewrite_hint.gif

(See "Specifying a Query Block in a Hint")

The `NO_REWRITE` hint instructs the optimizer to disable query rewrite for the query block, overriding the setting of the parameter `QUERY_REWRITE_ENABLED`. For example:

    SELECT /*+ NO_REWRITE */ sum(s.amount_sold) AS dollars
      FROM sales s, times t
      WHERE s.time_id = t.time_id
      GROUP BY t.calendar_month_desc;

### NOREWRITE Hint

The `NOREWRITE` hint has been deprecated. Use the `NO_REWRITE` hint instead.

### NO_QUERY_TRANSFORMATION Hint

Description of the illustration no_query_transformatn_hint.gif

The `NO_QUERY_TRANSFORMATION` hint instructs the optimizer to skip all query transformations, including but not limited to `OR`-expansion, view merging, subquery unnesting, star transformation, and materialized view rewrite. For example:

    SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
      FROM (SELECT *
            FROM employees e) v
      WHERE v.last_name = 'Smith';

### NO_STAR_TRANSFORMATION Hint

Description of the illustration no_star_transformation_hint.gif

(See "Specifying a Query Block in a Hint")

The `NO_STAR_TRANSFORMATION` hint instructs the optimizer not to perform star query transformation.

### NO_UNNEST Hint

Description of the illustration no_unnest_hint.gif

(See "Specifying a Query Block in a Hint")

Use of the `NO_UNNEST` hint turns off unnesting .

### NO_USE_HASH Hint

Description of the illustration no_use_hash_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `NO_USE_HASH` hint instructs the optimizer to exclude hash joins when joining each specified table to another row source using the specified table as the inner table. For example:

    SELECT /*+ NO_USE_HASH(e d) */ *
      FROM employees e, departments d
      WHERE e.department_id = d.department_id;

### NO_USE_MERGE Hint

Description of the illustration no_use_merge_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `NO_USE_MERGE` hint instructs the optimizer to exclude sort-merge joins when joining each specified table to another row source using the specified table as the inner table. For example:

    SELECT /*+ NO_USE_MERGE(e d) */ *
       FROM employees e, departments d
       WHERE e.department_id = d.department_id
       ORDER BY d.department_id;

### NO_USE_NL Hint

Description of the illustration no_use_nl_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `NO_USE_NL` hint instructs the optimizer to exclude nested loops joins when joining each specified table to another row source using the specified table as the inner table. For example:

    SELECT /*+ NO_USE_NL(l h) */ *
      FROM orders h, order_items l
      WHERE l.order_id = h.order_id
        AND l.order_id > 3500;

When this hint is specified, only hash join and sort-merge joins are considered for the specified tables. However, in some cases tables can be joined only by using nested loops. In such cases, the optimizer ignores the hint for those tables.

### NO_XML_QUERY_REWRITE Hint

Description of the illustration no_xml_query_rewrite_hint.gif

The `NO_XML_QUERY_REWRITE` hint instructs the optimizer to prohibit the rewriting of XPath expressions in SQL statements. For example:

    SELECT /*+NO_XML_QUERY_REWRITE*/ XMLQUERY('<A/>')
        FROM dual;

### ORDERED Hint

Description of the illustration ordered_hint.gif

The `ORDERED` hint instructs Oracle to join tables in the order in which they appear in the `FROM` clause. Oracle recommends that you use the `LEADING` hint, which is more versatile than the `ORDERED` hint.

When you omit the `ORDERED` hint from a SQL statement requiring a join, the optimizer chooses the order in which to join the tables. You might want to use the `ORDERED` hint to specify a join order if you know something that the optimizer does not know about the number of rows selected from each table. Such information lets you choose an inner and outer table better than the optimizer could.

The following query is an example of the use of the `ORDERED` hint:

    SELECT  /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
      FROM customers c, order_items l, orders o
      WHERE c.cust_last_name = :b1
        AND o.customer_id = c.customer_id
        AND o.order_id = l.order_id;

### PARALLEL Hint

Description of the illustration parallel_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `PARALLEL` hint instructs the optimizer to use the specified number of concurrent servers for a parallel operation. The hint applies to the `SELECT`, `INSERT`, `MERGE`, `UPDATE`, and `DELETE` portions of a statement, as well as to the table scan portion.

Note: The number of servers that can be used is twice the value in the `PARALLEL` hint, if sorting or grouping operations also take place.

If any parallel restrictions are violated, then the hint is ignored.

The `integer` value specifies the degree of parallelism for the specified table. Specifying `DEFAULT` or no value signifies that the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism. In the following example, the `PARALLEL` hint overrides the degree of parallelism specified in the `employees` table definition:

    SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name
      FROM employees hr_emp;

In the next example, the `PARALLEL` hint overrides the degree of parallelism specified in the `employees` table definition and instructs the optimizer to use the default degree of parallelism determined by the initialization parameters.

    SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name
      FROM employees hr_emp;

Oracle ignores parallel hints on temporary tables. Please refer to CREATE TABLE and Oracle Database Concepts for more information on parallel execution.

### PARALLEL_INDEX Hint

Description of the illustration parallel_index_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `PARALLEL_INDEX` hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans for partitioned indexes.

The `integer` value indicates the degree of parallelism for the specified index. Specifying `DEFAULT` or no value signifies that the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism. For example, the following hint indicates three parallel execution processes are to be used:

    SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */

### PQ_DISTRIBUTE Hint

Description of the illustration pq_distribute_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `PQ_DISTRIBUTE` hint instructs the optimizer how to distribute rows of joined tables among producer and consumer query servers. Such distribution can improve the performance of parallel join operations.

- `outer_distribution` is the distribution for the outer table.

- `inner_distribution` is the distribution for the inner table.

The values of the distributions are `HASH`, `BROADCAST`, `PARTITION`, and `NONE`. Only six combinations table distributions are valid, as described in Table 2-22:

Table 2-22 Distribution Hint Combinations

Distribution | Description
--- | ---
`HASH`, `HASH` | The rows of each table are mapped to consumer query servers, using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This distribution is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort merge join.
`BROADCAST`, `NONE` | All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This distribution is recommended when the outer table is very small compared with the inner table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is greater than the outer table size.
`NONE`, `BROADCAST` | All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This distribution is recommended when the inner table is very small compared with the outer table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is less than the outer table size.
`PARTITION`, `NONE` | The rows of the outer table are mapped using the partitioning of the inner table. The inner table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers. Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key.
`NONE`, `PARTITION` | The rows of the inner table are mapped using the partitioning of the outer table. The outer table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers. Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key.
`NONE`, `NONE` | Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equipartitioned on the join keys.

For example, given two tables `r` and `s` that are joined using a hash join, the following query contains a hint to use hash distribution:

    SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list
      FROM r,s
      WHERE r.c=s.c;

To broadcast the outer table `r`, the query is:

    SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list
      FROM r,s
      WHERE r.c=s.c;

See Also: Oracle Database Concepts for more information on how Oracle parallelizes join operations

### PUSH_PRED Hint

Description of the illustration push_pred_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `PUSH_PRED` hint instructs the optimizer to push a join predicate into the view. For example:

    SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
        FROM employees e,
             (SELECT manager_id
                FROM employees
             ) v
      WHERE e.manager_id = v.manager_id(+)
        AND e.employee_id = 100;

### PUSH_SUBQ Hint

Description of the illustration push_subq_hint.gif

(See "Specifying a Query Block in a Hint")

The `PUSH_SUBQ` hint instructs the optimizer to evaluate nonmerged subqueries at the earliest possible step in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then evaluating the subquery earlier can improve performance.

This hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.

### PX_JOIN_FILTER Hint

Description of the illustration px_join_filter_hint.gif

This hint forces the optimizer to use parallel join bitmap filtering.

### QB_NAME Hint

Description of the illustration qb_name_hint.gif

(See "Specifying a Query Block in a Hint")

Use the `QB_NAME` hint to define a name for a query block. This name can then be used in a hint in the outer query or even in a hint in an inline view to affect query execution on the tables appearing in the named query block.

If two or more query blocks have the same name, or if the same query block is hinted twice with different names, then the optimizer ignores all the names and the hints referencing that query block. Query blocks that are not named using this hint have unique system-generated names. These names can be displayed in the plan table and can also be used in hints within the query block, or in query block hints. For example:

    SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name
      FROM employees e
      WHERE last_name = 'Smith';

### REWRITE Hint

Description of the illustration rewrite_hint.gif

(See "Specifying a Query Block in a Hint")

The `REWRITE` hint instructs the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the `REWRITE` hint with or without a view list. If you use `REWRITE` with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.

Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of the cost of the final plan.

See Also: Oracle Database Concepts and Oracle Database Advanced Replication for more information on materialized views Oracle Data Warehousing Guide for more information on using `REWRITE` with materialized views

### RULE Hint

Description of the illustration rule_hint.gif

The `RULE` hint disables the use of the optimizer. This hint is not supported and should not be used.

### STAR_TRANSFORMATION Hint

Description of the illustration star_transformation_hint.gif

(See "Specifying a Query Block in a Hint")

The `STAR_TRANSFORMATION` hint instructs the optimizer to use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query. For example:

    SELECT /*+ STAR_TRANSFORMATION */  *
      FROM sales s, times t, products p, channels c
      WHERE s.time_id = t.time_id
        AND s.prod_id = p.product_id
        AND s.channel_id = c.channel_id
        AND p.product_status = 'obsolete';

Even if the hint is specified, there is no guarantee that the transformation will take place. The optimizer generates the subqueries only if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.

See Also: Oracle Data Warehousing Guide for a full discussion of star transformation. Oracle Database Reference for more information on the `STAR_TRANSFORMATION_ENABLED` initialization parameter.

### UNNEST Hint

Description of the illustration unnest_hint.gif

(See "Specifying a Query Block in a Hint")

The `UNNEST` hint instructs the optimizer to unnest and merge the body of the subquery into the body of the query block that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.

Before a subquery is unnested, the optimizer first verifies whether the statement is valid. The statement must then must pass heuristic and query optimization tests. The `UNNEST` hint instructs the optimizer to check the subquery block for validity only. If the subquery block is valid, then subquery unnesting is enabled without checking the heuristics or costs.

See Also: "Collection Unnesting: Examples" for more information on unnesting nested subqueries and the conditions that make a subquery block valid Oracle Database Performance Tuning Guide for additional information on subquery unnesting

### USE_CONCAT Hint

Description of the illustration use_concat_hint.gif

(See "Specifying a Query Block in a Hint")

The `USE_CONCAT` hint instructs the optimizer to transform combined `OR`-conditions in the `WHERE` clause of a query into a compound query using the `UNION` `ALL` set operator. Without this hint, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them. The `USE_CONCAT` hint overrides the cost consideration. For example:

    SELECT /*+ USE_CONCAT */ *
      FROM employees e
      WHERE manager_id = 108
         OR department_id = 110;

See Also: the "NO_EXPAND Hint", which is the opposite of this hint and Oracle Database Performance Tuning Guide for a discussion of `OR`-expansion

### USE_HASH Hint

Description of the illustration use_hash_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `USE_HASH` hint instructs the optimizer to join each specified table with another row source using a hash join. For example:

    SELECT /*+ USE_HASH(l h) */ *
      FROM orders h, order_items l
      WHERE l.order_id = h.order_id
        AND l.order_id > 3500;

### USE_MERGE Hint

Description of the illustration use_merge_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `USE_MERGE` hint instructs the optimizer to join each specified table with another row source using a sort-merge join. For example:

    SELECT /*+ USE_MERGE(employees departments) */ *
      FROM employees, departments
      WHERE employees.department_id = departments.department_id;

Use of the `USE_NL` and `USE_MERGE` hints is recommended with the `LEADING` and `ORDERED` hints. The optimizer uses those hints when the referenced table is forced to be the inner table of a join. The hints are ignored if the referenced table is the outer table.

### USE_NL Hint

The `USE_NL` hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table.

Description of the illustration use_nl_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The `USE_NL` hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table.

Use of the `USE_NL` and `USE_MERGE` hints is recommended with the `LEADING` and `ORDERED` hints. The optimizer uses those hints when the referenced table is forced to be the inner table of a join. The hints are ignored if the referenced table is the outer table.

In the following example, where a nested loop is forced through a hint, `orders` is accessed through a full table scan and the filter condition `l.order_id = h.order_id` is applied to every row. For every row that meets the filter condition, `order_items` is accessed through the index `order_id`.

    SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
      FROM orders h ,order_items l
      WHERE l.order_id = h.order_id;

Adding an `INDEX` hint to the query could avoid the full table scan on `orders`, resulting in an execution plan similar to one used on larger systems, even though it might not be particularly efficient here.

### USE_NL_WITH_INDEX Hint

Description of the illustration use_nl_with_index_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=, indexspec::=)

The `USE_NL_WITH_INDEX` hint instructs the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table. For example:

    SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *
      FROM orders h, order_items l
      WHERE l.order_id = h.order_id
        AND l.order_id > 3500;

The following conditions apply:

- If no index is specified, then the optimizer must be able to use some index with at least one join predicate as the index key.

- If an index is specified, then the optimizer must be able to use that index with at least one join predicate as the index key.